﻿using Md.Api.Entity.ModelDB;
using PetaPoco.NetCore;

namespace Md.Petapoco.Repositories.MdTrainingEducation;
/// <summary>
/// 课程视频仓储
/// </summary>
public class MdCourseRep : BaseRep<MdCourseModel>
{
    /// <summary>
    /// 获取课程信息
    /// </summary>
    /// <param name="courseId">课程编号</param>
    /// <returns>MdCourseModel</returns>
    public MdCourseModel GetMdCourse(long? courseId)
    {
        #region ===sql===
        string sqlStr = string.Format(@"
            select course_id
                  ,course_title
                  ,course_cover
                  ,course_video_file
                  ,course_desc
                  ,course_intro
                  ,course_price
                  ,course_classify_id
                  ,course_classify_name
                  ,course_teacher_id
                  ,course_teacher_name
                  ,course_subject_id
                  ,course_subject_name
                  ,course_sort
                  ,status
                  ,del_flag
                  ,remark
                  ,create_by
                  ,create_time
                  ,update_by
                  ,update_time
            from md_course
            where 1 = 1 and course_id = @0
        ");
        #endregion

        return MdTrainingEducationDB.GetInstance().SingleOrDefault<MdCourseModel>(sqlStr, courseId);
    }
    /// <summary>
    /// 获取课程列表数据
    /// </summary>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒序</param>
    /// <param name="model">筛选条件</param>
    /// <returns>IEnumerable<MdCourseModel></returns>
    public IEnumerable<MdCourseModel> GetMdCourseList(string? orderBy, bool? isDesc, MdCourseModel model)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (model.CourseId > 0)
        {
            sqlWhere += " and course_id = @0 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseTitle))
        {
            sqlWhere += " and course_title like @1 ";
        }
        if (model.CourseClassifyId > 0)
        {
            sqlWhere += " and course_classify_id = @2 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseClassifyName))
        {
            sqlWhere += " and course_classify_name like @3 ";
        }
        if (model.CourseTeacherId>0)
        {
            sqlWhere += " and course_teacher_id = @4 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseTeacherName))
        {
            sqlWhere += " and course_teacher_name like @5 ";
        }
        if (model.CourseSubjectId > 0)
        {
            sqlWhere += " and course_subject_id = @6 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseSubjectName))
        {
            sqlWhere += " and course_subject_name like @7 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and status = @8 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DelFlag))
        {
            sqlWhere += " and del_flag = @9 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and create_by = @10 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and update_by = @11 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select course_id
                  ,course_title
                  ,course_cover
                  ,course_video_file
                  ,course_desc
                  ,course_intro
                  ,course_price
                  ,course_classify_id
                  ,course_classify_name
                  ,course_teacher_id
                  ,course_teacher_name
                  ,course_subject_id
                  ,course_subject_name
                  ,course_sort
                  ,status
                  ,del_flag
                  ,remark
                  ,create_by
                  ,create_time
                  ,update_by
                  ,update_time
            from md_course
            where 1 = 1 {0} {1}
         ", sqlWhere, GetOrderByDesc(orderBy, isDesc));
        #endregion

        return MdTrainingEducationDB.GetInstance().Query<MdCourseModel>(
            sqlStr,
            model.CourseId,
            string.Format(@"%{0}%", model.CourseTitle),
            model.CourseClassifyId,
            string.Format(@"%{0}%", model.CourseClassifyName),
            model.CourseTeacherId,
            string.Format(@"%{0}%", model.CourseTeacherName),
            model.CourseSubjectId,
            string.Format(@"%{0}%", model.CourseSubjectName),
            model.Status,
            model.DelFlag,
            model.CreateBy,
            model.UpdateBy);
    }
    /// <summary>
    /// 分页获取课程列表数据
    /// </summary>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒序</param>
    /// <param name="pageIndex">当前页</param>
    /// <param name="pageSize">每页条数</param>
    /// <param name="model">筛选条件</param>
    /// <returns>Page<MdCourseModel></returns>
    public Page<MdCourseModel> GetMdCoursePageList(string? orderBy, bool? isDesc, int pageIndex, int pageSize, MdCourseModel model)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (model.CourseId > 0)
        {
            sqlWhere += " and course_id = @0 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseTitle))
        {
            sqlWhere += " and course_title like @1 ";
        }
        if (model.CourseClassifyId > 0)
        {
            sqlWhere += " and course_classify_id = @2 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseClassifyName))
        {
            sqlWhere += " and course_classify_name like @3 ";
        }
        if (model.CourseTeacherId > 0)
        {
            sqlWhere += " and course_teacher_id = @4 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseTeacherName))
        {
            sqlWhere += " and course_teacher_name like @5 ";
        }
        if (model.CourseSubjectId > 0)
        {
            sqlWhere += " and course_subject_id = @6 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseSubjectName))
        {
            sqlWhere += " and course_subject_name like @7 ";
        }
        if (model.CourseSubjectId > 0)
        {
            sqlWhere += " and course_subject_id = @8 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseSubjectName))
        {
            sqlWhere += " and course_subject_name like @9 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and status = @10 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DelFlag))
        {
            sqlWhere += " and del_flag = @11 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and create_by = @12 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and update_by = @13 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select course_id
                  ,course_title
                  ,course_cover
                  ,course_video_file
                  ,course_desc
                  ,course_intro
                  ,course_price
                  ,course_classify_id
                  ,course_classify_name
                  ,course_teacher_id
                  ,course_teacher_name
                  ,course_subject_id
                  ,course_subject_name
                  ,course_sort
                  ,status
                  ,del_flag
                  ,remark
                  ,create_by
                  ,create_time
                  ,update_by
                  ,update_time
            from md_course
            where 1 = 1 {0} {1}
         ", sqlWhere, GetOrderByDesc(orderBy, isDesc));
        #endregion

        return MdTrainingEducationDB.GetInstance().Page<MdCourseModel>(
            pageIndex,
            pageSize,
            sqlStr,
            model.CourseId,
            string.Format(@"%{0}%", model.CourseTitle),
            model.CourseClassifyId,
            string.Format(@"%{0}%", model.CourseClassifyName),
            model.CourseTeacherId,
            string.Format(@"%{0}%", model.CourseTeacherName),
            model.CourseSubjectId,
            string.Format(@"%{0}%", model.CourseSubjectName),
            model.CourseSubjectId,
            string.Format(@"%{0}%", model.CourseSubjectName),
            model.Status,
            model.DelFlag,
            model.CreateBy,
            model.UpdateBy);
    }
    /// <summary>
    /// 通过学生编号获取课程观看记录列表
    /// </summary>
    /// <param name="studentId">学生编号</param>
    /// <returns>IEnumerable<MdCourseModel></returns>
    public IEnumerable<MdCourseModel> GetMdCourseWatchListByStudentId(long studentId, MdCourseModel model)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (studentId > 0)
        {
            sqlWhere += " and b.student_id = @0 ";
        }
        if (model.CourseId > 0)
        {
            sqlWhere += " and a.course_id = @1 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseTitle))
        {
            sqlWhere += " and a.course_title like @2 ";
        }
        if (model.CourseClassifyId > 0)
        {
            sqlWhere += " and a.course_classify_id = @3 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseClassifyName))
        {
            sqlWhere += " and a.course_classify_name like @4 ";
        }
        if (model.CourseTeacherId > 0)
        {
            sqlWhere += " and a.course_teacher_id = @5 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseTeacherName))
        {
            sqlWhere += " and a.course_teacher_name like @6 ";
        }
        if (model.CourseSubjectId > 0)
        {
            sqlWhere += " and a.course_subject_id = @7 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CourseSubjectName))
        {
            sqlWhere += " and a.course_subject_name like @8 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and a.status = @9 ";
        }
        if (!string.IsNullOrWhiteSpace(model.DelFlag))
        {
            sqlWhere += " and a.del_flag = @10 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and a.create_by = @11 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and a.update_by = @12 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select a.course_id
                  ,a.course_title
                  ,a.course_cover
                  ,a.course_video_file
                  ,a.course_desc
                  ,a.course_intro
                  ,a.course_price
                  ,a.course_classify_id
                  ,a.course_classify_name
                  ,a.course_teacher_id
                  ,a.course_teacher_name
                  ,a.course_subject_id
                  ,a.course_subject_name
                  ,a.course_sort
                  ,a.status
                  ,a.del_flag
                  ,a.remark
                  ,a.create_by
                  ,a.create_time
                  ,a.update_by
                  ,a.update_time
            from md_course a
            right join md_course_watch b on a.course_id = b.course_id
            where 1 = 1 {0} order by b.create_time desc
         ", sqlWhere);
        #endregion

        return MdTrainingEducationDB.GetInstance().Query<MdCourseModel>(
            sqlStr,
            studentId,
            model.CourseId,
            string.Format(@"%{0}%", model.CourseTitle),
            model.CourseClassifyId,
            string.Format(@"%{0}%", model.CourseClassifyName),
            model.CourseTeacherId,
            string.Format(@"%{0}%", model.CourseTeacherName),
            model.CourseSubjectId,
            string.Format(@"%{0}%", model.CourseSubjectName),
            model.Status,
            model.DelFlag,
            model.CreateBy,
            model.UpdateBy);
    }
    /// <summary>
    /// 更新课程
    /// </summary>
    /// <param name="model">请求实体</param>
    /// <returns>int</returns>
    public long UpdateMdCourse(MdCourseModel model)
    {
        var setList = new List<string>();

        #region ===set===
        setList.Add(" course_title = @2 ");

        setList.Add(" course_cover = @3 ");

        setList.Add(" course_video_file = @4 ");

        setList.Add(" course_desc = @5 ");

        setList.Add(" course_intro = @6 ");

        setList.Add(" course_price = @7 ");

        setList.Add(" course_classify_id = @8 ");

        setList.Add(" course_classify_name = @9 ");

        setList.Add(" course_teacher_id = @10 ");

        setList.Add(" course_teacher_name = @11 ");

        setList.Add(" course_subject_id = @12 ");

        setList.Add(" course_subject_name = @13 ");

        setList.Add(" course_sort = @14 ");

        setList.Add(" status = @15 ");

        setList.Add(" update_by = @16 ");

        setList.Add(" update_time = @17 ");

        setList.Add(" remark = @18 ");
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
                set {0}
                where 1 = 1 and del_flag = @0 and course_id = @1
         ", string.Join(",", setList));
        #endregion

        var res = MdTrainingEducationDB.GetInstance().Update<MdCourseModel>(
            sqlStr,
            model.DelFlag,
            model.CourseId,
            model.CourseTitle,
            model.CourseCover,
            model.CourseVideoFile,
            model.CourseDesc,
            model.CourseIntro,
            model.CoursePrice,
            model.CourseClassifyId,
            model.CourseClassifyName,
            model.CourseTeacherId,
            model.CourseTeacherName,
            model.CourseSubjectId,
            model.CourseSubjectName,
            model.CourseSort,
            model.Status,
            model.UpdateBy,
            model.UpdateTime,
            model.Remark
         );

        return res;
    }
}

